<?php

class ItemMapper extends DataMapper
{
    const MODEL_CLASS = 'Item';

    // ------------- 更新系クエリ -----------------

    function insert(modelLoader $loader, $data)
    {
        $pdo = $this->getPDO();
        $modelClass = static::MODEL_CLASS;
        $editor_id = $loader->mapperParameters['editor_id'];

        $stmtHito = $pdo->prepare('
            INSERT INTO item(publish_date)
            VALUES (now())
        ');

        $stmtPost = $pdo->prepare('
            INSERT INTO post(editor_note)
            VALUES (?)
        ');
        $stmtPost->bindParam(1, $editor_note, PDO::PARAM_STR);

        $stmtPostHistory = $pdo->prepare('
            INSERT INTO post_history(editor_id, post_id)
            VALUES (?, ?)
        ');
        $stmtPostHistory->bindParam(1, $editor_id, PDO::PARAM_INT);
        $stmtPostHistory->bindParam(2, $post_id,   PDO::PARAM_STR);

        $stmtHitoHistory = $pdo->prepare('
            INSERT INTO item_history(history_id, item_id, name, note, kana, nickname, release_day, release_pre, type)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
        ');
        $stmtHitoHistory->bindParam(1, $history_id,  PDO::PARAM_INT);
        $stmtHitoHistory->bindParam(2, $item_id,     PDO::PARAM_INT);
        $stmtHitoHistory->bindParam(3, $name,        PDO::PARAM_STR);
        $stmtHitoHistory->bindParam(4, $note,        PDO::PARAM_STR);
        $stmtHitoHistory->bindParam(5, $kana,        PDO::PARAM_STR);
        $stmtHitoHistory->bindParam(6, $nickname,    PDO::PARAM_STR);
        $stmtHitoHistory->bindParam(7, $release_day, PDO::PARAM_STR);
        $stmtHitoHistory->bindParam(8, $release_pre, PDO::PARAM_INT);
        $stmtHitoHistory->bindParam(9, $type,        PDO::PARAM_INT);

        if (! is_array($data)) $data = array($data);
        
        foreach ($data as $row) {
            if (! $row instanceof $modelClass || $row->isValid()) {
                throw new InvalidArgumentException;
            }
            
            $stmtHito->execute();
            $item_id = $pdo->lastInsertId('item_item_id_seq');
            
            $editor_note= $row->editor_note;
            $stmtPost->execute();
            $post_id = $pdo->lastInsertId('post_post_id_seq');
            
            $stmtPostHistory->execute();
            $history_id = $pdo->lastInsertId('post_history_history_id_seq');
            
            $name       = $row->name;
            $kana       = $row->kana;
            $nickname   = $row->nickname;
            $note       = $row->note;
            $release_day = $row->release_day;
            $release_pre = $row->release_pre;
            $type       = $row->type;
            $stmtHitoHistory->execute();
        }
    }

    function update(modelLoader $loader, $data)
    {
        $pdo = $this->getPDO();
        $modelClass = static::MODEL_CLASS;
        $editor_id = $loader->mapperParameters['editor_id'];
        $post_id = $loader->solo;

        $stmtPost = $pdo->prepare('
            UPDATE post
               SET editor_note = ?
             WHERE post_id = ?
        ');
        $stmtPost->bindParam(1, $editor_note, PDO::PARAM_STR);
        $stmtPost->bindParam(2, $post_id,     PDO::PARAM_INT);

        $stmtPostHistory = $pdo->prepare('
            INSERT INTO post_history(editor_id, post_id)
            VALUES (?, ?)
        ');
        $stmtPostHistory->bindParam(1, $editor_id, PDO::PARAM_INT);
        $stmtPostHistory->bindParam(2, $post_id,   PDO::PARAM_STR);

        $stmtHitoHistory = $pdo->prepare('
            INSERT INTO item_history(history_id, item_id, name, note, kana, nickname, release_day, release_pre, type)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
        ');
        $stmtHitoHistory->bindParam(1, $history_id,  PDO::PARAM_INT);
        $stmtHitoHistory->bindParam(2, $item_id,     PDO::PARAM_INT);
        $stmtHitoHistory->bindParam(3, $name,        PDO::PARAM_STR);
        $stmtHitoHistory->bindParam(4, $note,        PDO::PARAM_STR);
        $stmtHitoHistory->bindParam(5, $kana,        PDO::PARAM_STR);
        $stmtHitoHistory->bindParam(6, $nickname,    PDO::PARAM_STR);
        $stmtHitoHistory->bindParam(7, $release_day, PDO::PARAM_STR);
        $stmtHitoHistory->bindParam(8, $release_pre, PDO::PARAM_INT);
        $stmtHitoHistory->bindParam(9, $type,        PDO::PARAM_INT);

        if (! is_array($data)) $data = array($data);
        
        foreach ($data as $row) {
            if (! $row instanceof $modelClass || $row->isValid()) {
                throw new InvalidArgumentException;
            }
            $editor_note= $row->editor_note;
            $item_id    = $row->item_id;
            $stmtPost->execute();
            
            $stmtPostHistory->execute();
            $history_id = $pdo->lastInsertId('post_history_history_id_seq');

            $name       = $row->name;
            $kana       = $row->kana;
            $nickname   = $row->nickname;
            $note       = $row->note;
            $release_day = $row->release_day;
            $release_pre = $row->release_pre;
            $type       = $row->type;
            $stmtHitoHistory->execute();
        }
    }

    function delete(modelLoader $loader, $data)
    {
        $pdo = $this->getPDO();
        
        $modelClass = static::MODEL_CLASS;

        $stmt = $pdo->prepare('
            DELETE FROM hito
             WHERE hito_id = ?
        ');
        $stmt->bindParam(1, $hito_id, PDO::PARAM_INT);

        if (! is_array($data))  $data = array($data);

        foreach ($data as $row) {
            if (! $row instanceof $modelClass) {
                throw new InvalidArgumentException;
            }
            $hito_id = $row->hito_id;
            $result = $stmt->execute();
        }
    }

    //------------- 参照系クエリ ----------------

    function findById(modelLoader $loader)
    {
        $history_id = @$loader->mapperParameters['history_id'];
        $where = ($history_id)
            ? "post_history.history_id = ?"
            : "post_history.history_id IN (SELECT max(temp.history_id) FROM post_history temp WHERE temp.post_id = ? GROUP BY temp.post_id)";
        
        $pdo = $this->getPDO();
        $sql = "
            SELECT
                   post_id, item.item_id, item_history.history_id, post_history.publish_date as update_date,
                   name, note, (SELECT editor_note FROM post WHERE post.post_id = post_history.post_id) editor_note,
                   kana, nickname, release_day, release_pre, type
              FROM item
              JOIN item_history ON item.item_id = item_history.item_id
              JOIN post_history ON post_history.history_id = item_history.history_id
             WHERE post_history.post_id = ?
                   AND {$where}
        ";
        $stmt = $pdo->prepare($sql);
        $id = $loader->solo;
        $stmt->bindParam(1, $id, PDO::PARAM_INT);
        if ($history_id) {
            $stmt->bindParam(2, $history_id, PDO::PARAM_INT);
        } else {
            $stmt->bindParam(2, $id, PDO::PARAM_INT);
        }
        $stmt->execute();
        
        $this->_decorate($stmt, $sql);
        return $stmt->fetch();
    }

    function find(modelLoader $loader, $countMode = false)
    {
        $pdo = $this->getPDO();
        
        $order = $this->getOrderString($loader->order);
        $where = $this->getSearchString($loader->alike, 'ilike', "name||'\n'||coalesce(kana,'')", $pdo);
        $limit = $this->getLimitString($loader->limit, $loader->offset);
        $without = @$loader->mapperParameters['without'];
        if ($without) {
            $without = explode(',', $without);
            $numbers = array();
            foreach ($without as $v) {
                if (is_numeric($v)) {
                    $numbers[] = $pdo->quote($v,PDO::PARAM_INT);
                }
            }
            if (!empty($numbers)) {
                $without = "post_id not in (".implode(',', $numbers).")";
                if ($where) $where = $where." AND ".$without;
                else $where = " WHERER ".$without;
            }
        }
        
        if (!$countMode) {
            $query = "
            SELECT
                   post_id, item.item_id, temp.history_id, post_history.publish_date as update_date,
                   name, note, (SELECT editor_note FROM post WHERE post.post_id = post_history.post_id) editor_note,
                   kana, nickname,
                   release_day, release_pre,
                   item_history.type,
                   (SELECT count(a.history_id) FROM item_history a WHERE a.item_id = item.item_id GROUP BY a.item_id ) _total
                   
              FROM item
              JOIN (SELECT max(a.history_id) history_id, a.item_id FROM item_history a GROUP BY a.item_id) temp ON temp.item_id = item.item_id
              JOIN item_history ON item_history.history_id = temp.history_id
              JOIN post_history ON post_history.history_id = temp.history_id
                  {$where}
                  {$order}
                  {$limit}
            ";
            $stmt = $pdo->query($query);
            $stmt->execute();
            return $this->_decorate($stmt, $query);
        } else {
            $query = '
                SELECT count(item.item_id) as _count,
                       '.$loader->limit.' as _limit
              FROM item
              JOIN (SELECT max(a.history_id) history_id, a.item_id FROM item_history a GROUP BY a.item_id) temp ON temp.item_id = item.item_id
              JOIN item_history ON item_history.history_id = temp.history_id
              JOIN post_history ON post_history.history_id = temp.history_id
                  '.$where.'
            ';
            
            $stmt = $pdo->query($query);
            $stmt->execute();
            $this->_decorate($stmt, $query);
            
            return $stmt->fetch();
        }
    }

    function findHistoryById(modelLoader $loader, $countMode = false)
    {
        $pdo = $this->getPDO();
        $item_id = $loader->mapperParameters['item_id'];
        
        $order = $this->getOrderString($loader->order);
        $limit = $this->getLimitString($loader->limit, $loader->offset);

        if (!$countMode) {
            $query = "
            SELECT
                   'link' as _mode,
                   history.history_id, history.parent_id as post_id,
                   (SELECT link.name FROM link WHERE link.link_id = history.link_id) as note,
                   history.editor_id as _member_id,
                   (SELECT name FROM member_history profile WHERE profile.member_id = history.editor_id ORDER BY history_id DESC LIMIT 1) as _member_name,
                   history.update_date as publish_date
              FROM link_history history
             WHERE  history.parent_id = ?
             UNION 
            SELECT
                   'edit' as _mode,
                   history.history_id, history.post_id,
                   name as note,
                   history.editor_id as _member_id,
                   (SELECT name FROM member_history profile WHERE profile.member_id = history.editor_id ORDER BY history_id DESC LIMIT 1) as _member_name,
                   history.publish_date as publish_date
              FROM item_history item
              LEFT JOIN post_history history ON history.history_id = item.history_id
             WHERE history.post_id = ?
                  {$order}
                  {$limit}
            ";
            $stmt = $pdo->prepare($query);
            $stmt->bindParam(1, $item_id, PDO::PARAM_INT);
            $stmt->bindParam(2, $item_id, PDO::PARAM_INT);
            $stmt->execute();
            return $this->_decorate($stmt, $query);
        } else {
            $query = '
            SELECT
                   (
            SELECT
                   count(history.history_id) count
              FROM link_history history
             WHERE  history.parent_id = ?
             ) +
                   (
            SELECT
                   count(history.history_id) count
              FROM item_history item
              LEFT JOIN post_history history ON history.history_id = item.history_id
             WHERE history.post_id = ?) as _count,
                   '.$loader->limit.' as _limit
            ';
            $stmt = $pdo->prepare($query);
            $stmt->bindParam(1, $item_id, PDO::PARAM_INT);
            $stmt->bindParam(2, $item_id, PDO::PARAM_INT);
            $stmt->execute();
            $this->_decorate($stmt, $query);
            
            return $stmt->fetch();
        }
    }
}